Imports System
Imports System.Collections
Imports System.Collections.Generic
Imports System.Runtime.Serialization
Imports System.Runtime.Serialization.Formatters.Binary
Imports System.IO


#Region "Public Class LayoutTemplate"
<Serializable()> Public Class LayoutTemplate
    Private _PrefixTemplate As String = String.Empty, _PostfixTemplate As String = "", _LayoutName As String = ""
    Private _LayoutFileName As String = String.Empty, _IsDirty As Boolean

    'prefix template file
    Public Sections As New Generic.List(Of SectionTemplate)

    Public Property IsDirty() As Boolean
        Get
            Return _IsDirty
        End Get

        Set(ByVal Value As Boolean)
            _IsDirty = Value
        End Set
    End Property

    Public Property PrefixTemplate() As String
        Get
            Return _PrefixTemplate
        End Get

        Set(ByVal Value As String)
            _PrefixTemplate = Value
        End Set
    End Property

    Public Property LayoutName() As String
        Get
            Return _LayoutName
        End Get

        Set(ByVal Value As String)
            _LayoutName = Value
        End Set
    End Property


    Public Property PostFixTemplate() As String
        Get
            Return _PostfixTemplate
        End Get

        Set(ByVal Value As String)
            _PostfixTemplate = Value
        End Set
    End Property

    Public Property FileName() As String
        Get
            Return _LayoutFileName
        End Get

        Set(ByVal Value As String)
            _LayoutFileName = Value
        End Set
    End Property

    Public Shared Function LoadLayout(ByVal strFileName) As LayoutTemplate
        Dim f As New BinaryFormatter
        Dim s = New FileStream(strFileName, FileMode.Open)
        Dim layoutTemplate As LayoutTemplate

        layoutTemplate = CType(f.Deserialize(s), LayoutTemplate)
        s.close()
        Return layoutTemplate


    End Function

    Public Sub New()
        _LayoutName = "New Layout Template"

    End Sub

End Class
#End Region

#Region "Public Class SectionTemplate"
<Serializable()> Public Class SectionTemplate
    Private _PrefixTemplate As String = "", _PostfixTemplate As String = "", _Description As String = ""
    Private _ProcessFields As Boolean, _SectionDetail As String = ""

    Public DataTypes As New Generic.List(Of DataType)
    Public Property PrefixTemplate() As String
        Get
            Return _PrefixTemplate
        End Get

        Set(ByVal Value As String)
            _PrefixTemplate = Value
        End Set
    End Property
    Public Property Detail() As String
        Get
            Return _SectionDetail
        End Get

        Set(ByVal Value As String)
            _SectionDetail = Value
        End Set
    End Property

    Public Property PostFixTemplate() As String
        Get
            Return _PostfixTemplate
        End Get

        Set(ByVal Value As String)
            _PostfixTemplate = Value
        End Set
    End Property

    Public Property Description() As String
        Get
            Return _Description
        End Get

        Set(ByVal Value As String)
            _Description = Value
        End Set
    End Property
End Class


#End Region

#Region "Public Class DataType"
<Serializable()> Public Class DataType
    'default flag
    'datatype
    'template file
    Dim _Type As String, _TypeDescription As String, _Template As String
    Dim _Description As String, _FieldType As String
    Dim _isKey As Boolean, _isForeignKey As Boolean

    Public Property IsKey() As Boolean
        Get
            Return _isKey
        End Get

        Set(ByVal Value As Boolean)
            _isKey = Value
        End Set
    End Property

    Public Property IsForeignKey() As Boolean
        Get
            Return _isForeignKey
        End Get

        Set(ByVal Value As Boolean)
            _isForeignKey = Value
        End Set
    End Property

    Public Property FieldType() As String
        Get
            Return _FieldType
        End Get

        Set(ByVal Value As String)
            _FieldType = Value
        End Set
    End Property


    Public Property Type() As String
        Get
            Return _Type
        End Get

        Set(ByVal Value As String)
            _Type = Value
        End Set
    End Property

    Public Property Template() As String
        Get
            Return _Template
        End Get

        Set(ByVal Value As String)
            _Template = Value
        End Set
    End Property

    Public Property Description() As String
        Get
            Return _Description
        End Get

        Set(ByVal Value As String)
            _Description = Value
        End Set
    End Property

End Class
#End Region

#Region "Public Class DatabaseServer"
<Serializable()> Public Class DatabaseServer
    Public Databases As New Generic.SortedDictionary(Of String, DatabaseStructure)
    Private _ConnectionString As String, _Username As String, _Password As String
    Private _Description As String, _NTAuthentication As Boolean
    Private _Name As String, _FileName As String, _IsDirty As Boolean

    Public Property FileName() As String
        Get
            Return _FileName
        End Get

        Set(ByVal Value As String)
            _FileName = Value
        End Set
    End Property

    Public Property UserName() As String
        Get
            Return _Username
        End Get

        Set(ByVal Value As String)
            _Username = Value
        End Set
    End Property

    Public Property Password() As String
        Get
            Return _Password
        End Get

        Set(ByVal Value As String)
            _Password = Value
        End Set
    End Property


    Public Property IsDirty() As Boolean
        Get
            Return _IsDirty
        End Get

        Set(ByVal Value As Boolean)
            _IsDirty = Value
        End Set
    End Property

    Public Property ConnectionString() As String
        Get
            Return _ConnectionString
        End Get

        Set(ByVal Value As String)
            _ConnectionString = Value
        End Set
    End Property

    Public Property Name() As String
        Get
            Return _Name
        End Get

        Set(ByVal Value As String)
            _Name = Value
        End Set
    End Property

    Public Property UseNTAuthentication() As Boolean
        Get
            Return _NTAuthentication
        End Get

        Set(ByVal Value As Boolean)
            _NTAuthentication = Value
        End Set
    End Property


    Public Sub New()
        _Name = "New database"
        _ConnectionString = ""
        _IsDirty = False
    End Sub

End Class

#End Region

#Region "Public Class DatabaseStructure"
<Serializable()> Public Class DatabaseStructure
    Public TableLayouts As New Generic.SortedDictionary(Of String, TableLayout)
    Private _ConnectionString As String, _Server As String
    Private _Description As String, _bUseTrustedConnection As Boolean = False
    Private _Name As String

    Public Property UseTrustedConnection() As Boolean
        Get
            Return _bUseTrustedConnection
        End Get

        Set(ByVal Value As Boolean)
            _bUseTrustedConnection = Value
        End Set
    End Property

    Public Property Server() As String
        Get
            Return _Server
        End Get

        Set(ByVal Value As String)
            _Server = Value
        End Set
    End Property

    Public Property ConnectionString() As String
        Get
            Return _ConnectionString
        End Get

        Set(ByVal Value As String)
            _ConnectionString = Value
        End Set
    End Property

    Public Property Name() As String
        Get
            Return _Name
        End Get

        Set(ByVal Value As String)
            _Name = Value
        End Set
    End Property
End Class

#End Region

#Region "Public Class TableLayout"

<Serializable()> Public Class TableLayout
    Public Columns As New Generic.Dictionary(Of String, Column)
    Private _TableName As String, _Description As String, _AltName As String
    Private _Properties As New Dictionary(Of String, String)

    Public Property Name() As String
        Get
            Return _TableName
        End Get

        Set(ByVal Value As String)
            _TableName = Value
        End Set
    End Property

    Public Property AltName() As String
        Get
            Return _AltName
        End Get

        Set(ByVal Value As String)
            _AltName = Value
        End Set
    End Property

    Public Property Description() As String
        Get
            Return _Description
        End Get

        Set(ByVal Value As String)
            _Description = Value

        End Set
    End Property
End Class


#End Region

#Region "Public Class Column"
<Serializable()> Public Class Column
    'default flag
    'datatype
    'template file
    Dim _Type As String, _ColumnName As String
    Public Values As New Dictionary(Of String, String)

    Dim _isKey As Boolean, _ignoreField As Boolean, _isForeignKey As Boolean

    Public Property Ignore() As Boolean
        Get
            Return _ignoreField
        End Get

        Set(ByVal Value As Boolean)
            _ignoreField = Value
        End Set
    End Property


    Public Property Type() As String
        Get
            Return _Type
        End Get

        Set(ByVal Value As String)
            _Type = Value
        End Set
    End Property

    Public Property Name() As String
        Get
            Return _ColumnName
        End Get

        Set(ByVal Value As String)
            _ColumnName = Value
        End Set
    End Property

    Public Property IsKey() As Boolean
        Get
            Return _isKey
        End Get

        Set(ByVal Value As Boolean)
            _isKey = Value
        End Set
    End Property

    Public Property IsForeignKey() As Boolean
        Get
            Return _isForeignKey
        End Get

        Set(ByVal Value As Boolean)
            _isForeignKey = Value
        End Set
    End Property
End Class
#End Region

#Region "Public Class GenerateParameters"
<Serializable()> Public Class GenerateParameters
    'default flag
    'datatype
    'template file
    Public Values As New Dictionary(Of String, String) 'Generic.List(Of Dictionary(Of String, String))
    Public FileName As String, _IsDirty As Boolean

    Public Property IsDirty() As Boolean
        Get
            Return _IsDirty
        End Get

        Set(ByVal Value As Boolean)
            _IsDirty = Value
        End Set
    End Property

    Public Sub New()
        'initialize default properties 
        Values.Add("TemplateFile", "")
        Values.Add("OutputDirectory", "")
        Values.Add("OutputLanguage", "VB")
        Values.Add("DatabaseFile", "")
        Values.Add("DatabaseName", "")
        Values.Add("TableName", "")
        Values.Add("Namespace", "")
        Values.Add("OutputFile", "")
        Values.Add("AuthorName", "")
        Values.Add("Modified", "")
        Values.Add("NameSpace", "")
        Values.Add("Assembly", "")
        Values.Add("ShortDateTime", "")
        Values.Add("LongDateTime", "")
        Values.Add("ClassName", "")
        _IsDirty = False
    End Sub
End Class
#End Region

#Region "Public Class GenerateCode"
<Serializable()> Public Class GenerateCode
    Public Values As New Dictionary(Of String, String)

    Private layout As LayoutTemplate, tableLayout As TableLayout
    Private dbServer As DatabaseServer, generateProperties As New GenerateParameters
    Private outputLanguage As String, lastField As Boolean, lastKey As String, lastNonKey As String
    Public Sub LoadTemplate(Optional ByVal fileName As String = "")
        If fileName = "" Then fileName = generateProperties.Values("TemplateFile")
        layout = Utils.LoadSerializedFile(fileName)
    End Sub

    Public Sub LoadDatabaseStructure(Optional ByVal fileName As String = "")

        If fileName = "" Then fileName = generateProperties.Values("DatabaseFile")
        dbServer = Utils.LoadSerializedFile(fileName)
    End Sub

    Public Sub LoadGenerateProperties(ByVal fileName As String)
        generateProperties = Utils.LoadSerializedFile(fileName)
    End Sub

    Public Sub ModifyProperty(ByVal valKey, ByVal newVal)

    End Sub


    Sub AddUpdateDictionary(ByVal dict As Generic.Dictionary(Of String, String), ByVal key As String, ByVal value As String)

        If dict.ContainsKey(key) Then
            dict(key) = value
        Else
            dict.Add(key, value)
        End If
    End Sub

    Function BuildVariable(ByVal buildLanguage As String, ByVal variableName As String, ByVal dataType As String) As String

        If buildLanguage = "VB" Then
            BuildVariable = variableName & " AS " & dataType & ","
        Else 'assume C#

            dataType = GetNETType(buildLanguage, dataType)

            'dataType = dataType.ToLower

            BuildVariable = dataType & " " & variableName & ","
        End If

    End Function

    Function GetNETType(ByVal buildLanguage As String, ByVal dataType As String) As String

        If buildLanguage = "VB" Then
            GetNETType = dataType
        Else 'assume C#

            'convert a few VB data types to C#
            Select Case dataType
                Case "Boolean"
                    dataType = "bool"
                Case "Integer"
                    dataType = "int"
                Case "Single"
                    dataType = "float"
                Case "Single"
                    dataType = "float"
                Case "String"
                    dataType = "string"
                Case "Date"
                    dataType = "DateTime"
            End Select

            'dataType = dataType.ToLower

            GetNETType = dataType
        End If

    End Function


    Public Sub GenerateOutput()
        Dim templateSection As SectionTemplate, col As Column
        Dim outputText As String, allFields As String = String.Empty, allFieldsNoKeys As String = String.Empty
        Dim allKeys As String = String.Empty, dataType As String, columnCount As Integer
        Dim ordinalAll As Integer = 0



        tableLayout = dbServer.Databases(generateProperties.Values("DatabaseName")).TableLayouts(generateProperties.Values("TableName"))

        AddUpdateDictionary(generateProperties.Values, "TableName", tableLayout.Name)
        AddUpdateDictionary(generateProperties.Values, "TableDescription", tableLayout.Description)
        AddUpdateDictionary(generateProperties.Values, "TableAltName", tableLayout.AltName)

        'determine language (C# or VB)
        outputLanguage = generateProperties.Values("OutputLanguage")


        'build field global properties
        For Each kvptl As KeyValuePair(Of String, Column) In tableLayout.Columns
            col = kvptl.Value
            If Not col.Ignore Then

                AddUpdateDictionary(col.Values, "ProperNETDataType", col.Values("NETDataType"))
                dataType = GetNETType(outputLanguage, col.Values("NETDataType"))

                col.Values("NETDataType") = dataType

                allFields = allFields & BuildVariable(outputLanguage, col.Name, dataType)

                'Nullable(Of DateTime)

                If Not col.IsKey Then
                    allFieldsNoKeys = allFieldsNoKeys & BuildVariable(outputLanguage, col.Name, dataType)
                End If

                If col.IsKey Then
                    allKeys = allKeys & BuildVariable(outputLanguage, col.Name, dataType)
                    AddUpdateDictionary(generateProperties.Values, "KeyField", dataType)
                End If

            End If
        Next
        'strip commas from field variables
        allFields = allFields.TrimEnd(",")
        allKeys = allKeys.TrimEnd(",")
        allFieldsNoKeys = allFieldsNoKeys.TrimEnd(",")

        'add field values to property collection
        AddUpdateDictionary(generateProperties.Values, "AllFieldsNoKeys", allFieldsNoKeys)
        AddUpdateDictionary(generateProperties.Values, "AllFields", allFields)
        AddUpdateDictionary(generateProperties.Values, "AllKeys", allKeys)

        'update date time properties
        AddUpdateDictionary(generateProperties.Values, "LongDate", Now.ToLongDateString)
        AddUpdateDictionary(generateProperties.Values, "ShortDate", Now.ToShortDateString)
        AddUpdateDictionary(generateProperties.Values, "LongTime", Now.ToLongTimeString)
        AddUpdateDictionary(generateProperties.Values, "ShortTime", Now.ToShortTimeString)

        AddUpdateDictionary(generateProperties.Values, "FieldCount", "0")


        outputText = ProcessTemplate(layout.PrefixTemplate)


        For Each kvptl As KeyValuePair(Of String, Column) In tableLayout.Columns
            col = kvptl.Value

            If col.IsKey Then lastKey = col.Name
            lastNonKey = col.Name
        Next

        'process each section
        For Each templateSection In layout.Sections
            'loop through properties and replace any values
            outputText = outputText & ProcessTemplate(templateSection.PrefixTemplate)

            lastField = False
            columnCount = 1
            'process each column
            For Each kvptl As KeyValuePair(Of String, Column) In tableLayout.Columns
                col = kvptl.Value


                If Not col.Ignore Then
                    'update ordinal position
                    outputText = outputText & ProcessColTemplate(templateSection, col)
                    ordinalAll = ordinalAll + 1
                End If

                columnCount = columnCount + 1

            Next

            outputText = outputText & ProcessTemplate(templateSection.PostFixTemplate)
        Next
        outputText = outputText & ProcessTemplate(layout.PostFixTemplate)

        'write the results to text file
        My.Computer.FileSystem.WriteAllText(generateProperties.Values("OutputDirectory") & generateProperties.Values("OutputFile"), outputText, False)

    End Sub

    Function ProcessTemplate(ByVal templateText As String) As String

        For Each kvp As KeyValuePair(Of String, String) In generateProperties.Values
            templateText = templateText.Replace("<#" & kvp.Key & "#>", kvp.Value)
        Next

        ProcessTemplate = templateText
    End Function


    Function ProcessColTemplate(ByVal templateSection As SectionTemplate, ByVal col As Column) As String
        Dim replaceTemplate As String, dataType As String
        Dim dt As DataType, bMatch As Boolean
        'data types, string, int numbers, decimal numbers, datetime 

        dataType = col.Values("DATA_TYPE")

        'get the default section template
        replaceTemplate = templateSection.Detail

        If lastNonKey = col.Name Then
            lastField = True
        End If

        'if specific data type criteria then check each field
        If templateSection.DataTypes.Count > 0 Then
            'check each data type
            For Each dt In templateSection.DataTypes

                '
                replaceTemplate = String.Empty
                bMatch = False
                If dt.Type = dataType Then
                    bMatch = True
                ElseIf dt.Type = "String Types" And InStr("char,nchar,varchar,text,ntext,nvarchar", dataType) > 0 Then
                    bMatch = True
                ElseIf dt.Type = "Integer Types" And InStr("int,bigint,smallint,tinyint", dataType, ) > 0 Then
                    bMatch = True
                ElseIf dt.Type = "Decimal Type" And InStr("real,numeric,money,smallmoney,float,decimal", dataType) > 0 Then
                    bMatch = True
                ElseIf dt.Type = "Date Types" And InStr("smalldatetime,datetime,timestamp", dataType) > 0 Then
                    bMatch = True
                ElseIf dt.Type = "Any Data Type" Then
                    bMatch = True
                End If


                'determine if correct type of field
                If bMatch Then

                    lastField = False
                    If lastNonKey = col.Name Then
                        lastField = True
                    End If

                    If dt.FieldType = "All fields" Then
                        bMatch = True
                    ElseIf dt.FieldType = "All fields except primary keys" Then
                        If col.IsKey Then
                            bMatch = False
                        End If

                    ElseIf dt.FieldType = "Primary keys only" Then
                        If Not col.IsKey Then
                            bMatch = False
                        End If

                        'determine if last column in collection
                        If col.Name = lastKey Then
                            lastField = True
                        Else
                            lastField = False
                        End If

                    ElseIf dt.FieldType = "Foreign keys only" Then
                        If Not col.IsForeignKey Then
                            bMatch = False
                        End If
                    End If
                End If

                If bMatch Then
                    replaceTemplate = dt.Template
                    Exit For
                End If

            Next
        End If

        replaceTemplate = replaceTemplate.Replace("<#FieldName#>", col.Name)

        'loop through each column value in dictionary
        For Each kvp As KeyValuePair(Of String, String) In col.Values
            replaceTemplate = replaceTemplate.Replace("<#" & kvp.Key & "#>", kvp.Value)
        Next

        replaceTemplate = ReplaceFunctions(replaceTemplate)

        ProcessColTemplate = replaceTemplate
    End Function


    Public Function ReplaceFunctions(ByVal replaceTemplate As String)
        Dim functionStart As Integer, functionEnd As Integer, functionName As String, fullFunction As String

        functionStart = InStr(replaceTemplate, "<#")
        Do While functionStart > 0
            functionEnd = InStr(functionStart + 3, replaceTemplate, "#>")
            '
            functionName = replaceTemplate.Substring(functionStart + 1, (functionEnd - functionStart) - 2)
            fullFunction = replaceTemplate.Substring(functionStart - 1, (functionEnd - functionStart) + 2)

            If Left(functionName, 3) = "REP" Then
                If Not lastField Then
                    replaceTemplate = replaceTemplate.Replace(fullFunction, functionName.Substring(3))
                Else
                    replaceTemplate = replaceTemplate.Replace(fullFunction, "")
                End If
                functionEnd = 1
            Else
                functionEnd = functionEnd + 2
            End If

            functionStart = InStr(functionEnd, replaceTemplate, "<#")
        Loop

        ReplaceFunctions = replaceTemplate
    End Function

    Public Property GenerateParameters() As GenerateParameters
        Get
            Return generateProperties
        End Get
        Set(ByVal Value As GenerateParameters)
            generateProperties = Value
        End Set
    End Property


End Class
#End Region


Public Class Utils

    Public Shared Function LoadSerializedFile(ByVal strFileName As String) As Object
        Dim f As New BinaryFormatter
        Dim s As FileStream

        Try
            'open file
            s = New FileStream(strFileName, FileMode.Open)

        Catch ex As Exception

        End Try

        If s.Length = 0 Then
            LoadSerializedFile = Nothing
        Else
            LoadSerializedFile = f.Deserialize(s)
        End If
        s.Close()
    End Function

    Public Shared Sub SaveFile(ByVal strPath, ByVal serializeobject)
        Dim s As Stream
        Dim b As BinaryFormatter = New BinaryFormatter()

        s = New FileStream(strPath, FileMode.Create)

        b.Serialize(s, serializeobject)
        s.Close()

    End Sub


End Class

